-- This file assumes that you have executed 05_certsignserver.sql.
SET NOCOUNT ON
USE master
go

-- To be able to sign a procedure in the PermTest database with the
-- DemoServerCert, we need to export it from master to PermTest.
-- First we do this with BACKUP CERTIFICATE. We must specify a password
-- to protect the private key.
BACKUP CERTIFICATE DemoServerCert
    TO FILE='C:\temp\DemoServerCert.cer'
WITH PRIVATE KEY
   (FILE = 'C:\temp\DemoServerCert.pvk',
    DECRYPTION BY PASSWORD = N'Nic nie boli, tak jak życie',
    ENCRYPTION BY PASSWORD = N'Nic nie boli, tak jak życie')

-- Move over to the PermTest database.
USE PermTest
go
-- Import the certificate from the files. Note that we can select a new
-- password for the cert in this database.
CREATE CERTIFICATE DemoServerCert
    FROM FILE='C:\temp\DemoServerCert.cer'
WITH PRIVATE KEY
    (FILE = 'C:\temp\DemoServerCert.pvk',
     DECRYPTION BY PASSWORD = N'Nic nie boli, tak jak życie',
     ENCRYPTION BY PASSWORD = N'Nic nie boli, tak jak życie')

-- Delete the certificate from disk, so that next export does not fail.
EXEC xp_cmdshell 'DEL C:\temp\DemoServerCert.*', 'no_output'
go
-- We need the view for login-token information in PermTest as well.
-- Create the view with logintokeninfo also in PermTest
CREATE VIEW logintokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.login_token
go
-- Create a database-local version of sp_showusersindb to be more supported.
CREATE PROCEDURE ShowSessions AS
   SELECT * FROM logintokeninfo

   IF is_srvrolemember('SuperUsers') = 1 OR
      has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
     SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
     RAISERROR('You are not permitted to run this procedure', 16, 1)
go
ADD SIGNATURE TO ShowSessions BY CERTIFICATE DemoServerCert
    WITH PASSWORD = N'Nic nie boli, tak jak życie'
go

-- Play our super user.
EXECUTE AS LOGIN = 'PermTestSuper'
go
EXECUTE ShowSessions
go
REVERT

-- Drop signature and certificate for now.
DROP SIGNATURE FROM ShowSessions BY CERTIFICATE DemoServerCert
DROP CERTIFICATE DemoServerCert

-- Back to master for the second way to export. Works on SQL 2012 and up.
USE master
go
-- Here is the concept:
DECLARE @public_key varbinary(MAX) =
            certencoded(cert_id('DemoServerCert')),
        @private_key varbinary(MAX) =
            certprivatekey(cert_id('DemoServerCert'),
                           N'Nic nie boli, tak jak życie',
                           N'Nic nie boli, tak jak życie')
-- Change database.
USE PermTest
-- Alas, this syntax is not valid - the binary values must be literal.
CREATE CERTIFICATE DemoServerCert
FROM BINARY = @public_key
WITH PRIVATE KEY
   (BINARY = @private_key,
    DECRYPTION BY PASSWORD = N'Nic nie boli, tak jak życie',
    ENCRYPTION BY PASSWORD = N'Nic nie boli, tak jak życie')
go
-- We need to use dynamic SQL, sigh.
USE master
go
DECLARE @public_key varbinary(MAX) =
            certencoded(cert_id('DemoServerCert')),
        @private_key varbinary(MAX) =
            certprivatekey(cert_id('DemoServerCert'),
                           N'Nic nie boli, tak jak życie',
                           N'Nic nie boli, tak jak życie'),
        @sql nvarchar(MAX)

-- Add SELECT so if the SQL comes out blank, you can see which key you did not retrieve.
--SELECT @public_key, @private_key

SELECT @sql =
   N'CREATE CERTIFICATE DemoServerCert
     FROM BINARY = ' + convert(nvarchar(MAX), @public_key, 1) + N'
     WITH PRIVATE KEY
         (BINARY = ' + convert(nvarchar(MAX), @private_key, 1) + N',
          DECRYPTION BY PASSWORD = N''Nic nie boli, tak jak życie'',
          ENCRYPTION BY PASSWORD = N''Nic nie boli, tak jak życie'')'

PRINT  @sql
-- Execute the batch in PermTest
EXEC PermTest..sp_executesql @sql
go

-- Move to PermTest again
USE PermTest
go
-- Sign the procedure anew.
ADD SIGNATURE TO ShowSessions BY CERTIFICATE DemoServerCert
                 WITH PASSWORD = N'Nic nie boli, tak jak życie'
go
-- Drop the private key.
ALTER CERTIFICATE DemoServerCert REMOVE PRIVATE KEY
go
-- The super user can run the procedure.
EXECUTE AS LOGIN = 'PermTestSuper'
go
EXECUTE ShowSessions
go
REVERT

